using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Common;
using Garlic.CC.Utility;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

using Garlic.DM.DataModel;

namespace Garlic.DAL
{
	/// <summary>
	/// DataAccess
	/// </summary>
	public partial class RoleDAL
	{
		#region Sql const
		public  const string TS_BAS_ROLE_SELECT_BY_ID=" SELECT role_id,role_name,role_type,comments,create_user,create_time,modify_user,modify_time"+
											" FROM TS_BAS_Role"+
											" WHERE 1=1 " +
											" and  role_id =  :role_id";

											
		public  const string TS_BAS_ROLE_SELECT=" SELECT "+
											"role_id,"+
											"role_name,"+
											"role_type,"+
											"comments,"+
											"create_user,"+
											"create_time,"+
											"modify_user,"+
											"modify_time"+
											" FROM TS_BAS_Role {0}";
														
								
	
		public  const string TS_BAS_ROLE_INSERT=" INSERT INTO TS_BAS_Role ("+
											"role_id,"+
											"role_name,"+
											"role_type,"+
											"comments,"+
											"create_user,"+
											"create_time,"+
											"modify_user,"+
											"modify_time"+
										") VALUES ("+
											":role_id,"+
											":role_name,"+
											":role_type,"+
											":comments,"+
											":create_user,"+
											":create_time,"+
											":modify_user,"+
											":modify_time"+
										")";
									
									
		public  const string TS_BAS_ROLE_UPDATE=" UPDATE TS_BAS_Role SET "+
											" role_name =  :role_name, "+
											" role_type =  :role_type, "+
											" comments =  :comments, "+
											" create_user =  :create_user, "+
											" create_time =  :create_time, "+
											" modify_user =  :modify_user, "+
											" modify_time =  :modify_time "+
										" WHERE 1=1 " +
											" and  role_id =  :role_id";
										
	
	
		public  const string TS_BAS_ROLE_DELETE=" DELETE FROM TS_BAS_Role" +
										" WHERE 1=1"+
											" and  role_id =  :role_id";
										
										
		
		#endregion
		
		/// <summary>
		/// Get RoleInfo by role_id
		/// </summary>
		/// <param name="role_id">RoleInfo </param>
		/// <returns></returns>
		public static RoleInfo GetInfo(int role_id)
		{
	
				Database db = DatabaseFactory.CreateDatabase();
				DbCommand dbCommand = db.GetSqlStringCommand(TS_BAS_ROLE_SELECT_BY_ID);
			db.AddInParameter(dbCommand, "role_id", DbType.Int32,role_id);
				

				using (IDataReader dr = db.ExecuteReader(dbCommand))
				{
					if (dr.Read())
					{
						return CreateRoleInfo(dr);
					}
					else
						return null;
				}
		}
	
	
		/// <summary>
		/// Get data list
		/// </summary>
		/// <param name="textWhere">conditon</param>
		/// <param name="orderText">sort</param>
		/// <returns>RoleInfo list </returns>
		public static List<RoleInfo> GetList(string textWhere,string orderText)
		{
			string query=string.Empty;
			if(!string.IsNullOrEmpty(textWhere))
			{
			query+=" where "+textWhere; 
			}
			
			if(!string.IsNullOrEmpty(orderText))
			{
			query+=" order by "+orderText;
			}
			
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(string.Format(TS_BAS_ROLE_SELECT,query));
			
			return GetList(string.Format(TS_BAS_ROLE_SELECT,query));
			
		}
		
		/// <summary>
		/// Get data list
		/// </summary>
		/// <param name="sql">SQL statement</param>
		/// <returns>RoleInfo list </returns>
		public static List<RoleInfo> GetList(string sql)
		{
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(sql);
			List<RoleInfo> list=new List<RoleInfo>();
			using (IDataReader dr = db.ExecuteReader(dbCommand))
			{
				while(dr.Read())
				{
					list.Add(CreateRoleInfo(dr));
				}
			}
			
			return list;
		}

	
		/// <summary>
		/// Add
		/// </summary>
		/// <param name="roleInfo"> entity</param>
		public static int Add(RoleInfo roleInfo)
		{
				Database db = DatabaseFactory.CreateDatabase();
	
				DbCommand dbCommand = db.GetSqlStringCommand(TS_BAS_ROLE_INSERT);
				
				db.AddInParameter(dbCommand, "role_id",DbType.Int32,roleInfo.Role_id); 
				db.AddInParameter(dbCommand, "role_name",DbType.AnsiString,DBConvert.ToDBValue(roleInfo.Role_name)); 
				db.AddInParameter(dbCommand, "role_type",DbType.Int32,DBConvert.ToDBValue(roleInfo.Role_type)); 
				db.AddInParameter(dbCommand, "comments",DbType.AnsiString,DBConvert.ToDBValue(roleInfo.Comments)); 
				db.AddInParameter(dbCommand, "create_user",DbType.Int32,DBConvert.ToDBValue(roleInfo.Create_user)); 
				db.AddInParameter(dbCommand, "create_time",DbType.DateTime,DBConvert.ToDBValue(roleInfo.Create_time)); 
				db.AddInParameter(dbCommand, "modify_user",DbType.Int32,DBConvert.ToDBValue(roleInfo.Modify_user)); 
				db.AddInParameter(dbCommand, "modify_time",DbType.DateTime,DBConvert.ToDBValue(roleInfo.Modify_time)); 
				
				return int.Parse("0"+db.ExecuteScalar(dbCommand));
		}
		
		/// <summary>
		/// Update
		/// </summary>
		/// <param name="roleInfo"> utility</param>
		public static void Update(RoleInfo roleInfo)
		{
			Database db = DatabaseFactory.CreateDatabase();
	
				DbCommand dbCommand = db.GetSqlStringCommand(TS_BAS_ROLE_UPDATE);
				
				db.AddInParameter(dbCommand, "role_id",DbType.Int32,roleInfo.Role_id); 
				db.AddInParameter(dbCommand, "role_name",DbType.AnsiString,DBConvert.ToDBValue(roleInfo.Role_name)); 
				db.AddInParameter(dbCommand, "role_type",DbType.Int32,DBConvert.ToDBValue(roleInfo.Role_type)); 
				db.AddInParameter(dbCommand, "comments",DbType.AnsiString,DBConvert.ToDBValue(roleInfo.Comments)); 
				db.AddInParameter(dbCommand, "create_user",DbType.Int32,DBConvert.ToDBValue(roleInfo.Create_user)); 
				db.AddInParameter(dbCommand, "create_time",DbType.DateTime,DBConvert.ToDBValue(roleInfo.Create_time)); 
				db.AddInParameter(dbCommand, "modify_user",DbType.Int32,DBConvert.ToDBValue(roleInfo.Modify_user)); 
				db.AddInParameter(dbCommand, "modify_time",DbType.DateTime,DBConvert.ToDBValue(roleInfo.Modify_time)); 
				
				db.ExecuteNonQuery(dbCommand);
		}
		
		/// <summary>
		/// Delete
		/// </summary>
		/// <param name="role_id">TS_BAS_Role Number</param>
		/// <returns></returns>
		public static void Delete(int role_id)
		{
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(TS_BAS_ROLE_DELETE);
			db.AddInParameter(dbCommand, "role_id", DbType.Int32,role_id);
			db.ExecuteNonQuery(dbCommand);
		}
		
	

	
		private static RoleInfo CreateRoleInfo(IDataReader rdr)
		{
			RoleInfo myroleInfo = new RoleInfo();
			myroleInfo.Role_id=rdr.GetInt32(0);
			myroleInfo.Role_name=DBConvert.GetString(rdr,1);
			myroleInfo.Role_type=DBConvert.GetInt32(rdr,2);
			myroleInfo.Comments=DBConvert.GetString(rdr,3);
			myroleInfo.Create_user=DBConvert.GetInt32(rdr,4);
			myroleInfo.Create_time=DBConvert.GetDateTime(rdr,5);
			myroleInfo.Modify_user=DBConvert.GetInt32(rdr,6);
			myroleInfo.Modify_time=DBConvert.GetDateTime(rdr,7);

			return myroleInfo;
		}
	}
}
